﻿using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.EditorInput;
using Autodesk.AutoCAD.Geometry;
using Autodesk.AutoCAD.Runtime;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using YZQ_DotNetArx.CAD;
using System.Data;
using System.Data.SQLite;
using DataTable = System.Data.DataTable;
using System.Dynamic;

namespace YZQ_DotNetArx.Test
{
    public static class TestRoom
    {
        [CommandMethod("TRoom")]
        public static void TRoom()
        {
            DocumentLock documentLock = Env.Document.LockDocument();
            PromptOpenFileOptions pfo = new PromptOpenFileOptions("打开数据文件对话框");
            pfo.Filter = "txt file(*.txt)|*.txt|All File(*.*)|*.*";
            PromptFileNameResult pfr = Env.Editor.GetFileNameForOpen(pfo);
            List<string> listNums = new List<string>();
            if (pfr.Status == PromptStatus.OK)
            {
                string strName = pfr.StringResult;
                if (strName == "") return;
                string[] contens = File.ReadAllLines(strName);

                for (int i = 0; i < contens.Length; i++)
                {
                    List<string> tempList = new List<string>();
                    tempList = contens[i].Split(new char[] { ',' }).ToList();
                    listNums.AddRange(tempList);
                }
            }
            List<Point3d> pts=new List<Point3d>();
            for (int i = 0; i < listNums.Count/3; i++)
            {
                Point3d pt= new Point3d(Convert.ToDouble(listNums[i * 3]), Convert.ToDouble(listNums[i * 3 + 1]), Convert.ToDouble(listNums[i * 3 + 2]));
                pts.Add(pt);
            }
            Polyline pl= new Polyline();    
            pl.CreatePolyline(pts.ToArray());
            pl.Closed=true;
            Env.AddEntityToModelSpace(pl);
            //List<Circle> circles = new List<Circle>();
            //for (int i = 0; i < listNums.Count / 3; i++)
            //{
            //    Circle circle = new Circle();
            //    circle.Radius = 0.5;
            //    circle.Center = new Point3d(Convert.ToDouble(listNums[i * 3]), Convert.ToDouble(listNums[i * 3 + 1]), Convert.ToDouble(listNums[i * 3 + 2]));
            //    circles.Add(circle);
            //}
            //Env.AddEntityToModelSpace(circles);
        }
        [CommandMethod("CSDB")]
        public static void CSDB()
        {
            string conString = "DataSource=C:\\Users\\10635\\Desktop\\jgdatabase.db";
            List<string> tableName= GetTableNames(conString);
            Env.Print("/n表名.......");
            foreach (var item in tableName)
            {
                Env.Print(item);
            }
            Env.Print(tableName.Count);
            List<string> colNames = GetTableColumns(conString,"TXA");
            Env.Print("/n字段.......");
            foreach (var item in colNames)
            {
                Env.Print(item);
            }
            Env.Print("/n数据.......");
            List<string> strings = QueryData(conString,"TXA", "points");
            foreach (var item in strings)
            {
                Env.Print(item);
            }
        }
        /// <summary>
        /// /查询数据库中的全部表名
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static List<string> GetTableNames(string connectionString)
        {
            List<string> tableNames = new List<string>();

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                SQLiteCommand command = new SQLiteCommand("SELECT name FROM sqlite_master WHERE type='table'", connection);
                SQLiteDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    string tableName = reader.GetString(0);
                    tableNames.Add(tableName);
                }
            }
            return tableNames;
        }
        /// <summary>
        /// 查询数据库中指定表的所有字段
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static List<string> GetTableColumns(string connectionString, string tableName)
        {
            List<string> columns = new List<string>();
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                DataTable schema = connection.GetSchema("Columns", new string[] { null, null, tableName });
                foreach (DataRow row in schema.Rows)
                {
                    string columnName = (string)row["COLUMN_NAME"];
                    columns.Add(columnName);
                }
            }
            return columns;
        }
        public static List<string> QueryData(string connectionString, string tableName, string columnName)
        {
            List<string> results = new List<string>();
            string query = $"SELECT {columnName} FROM {tableName}";
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(query, connection))
                {
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            results.Add(reader[columnName].ToString());
                        }
                    }
                }
            }
            return results;
        }
    }
}
